﻿namespace MS_SqlServer
{
    class sss
    {
        void CenterYearReport()
        {
            
            var sqlTxt = string.Format(
            @"Select b.CompanyName 
,b.CenterName
,b.Year,b.Month
,b.Amount 
,t.CompanyName as tName
,t.TargetAmount 
,Cast(Round(100*b.Amount /t.TargetAmount,2) as varchar)+'%' as Rate
from 
(
Select Billing.CompanyName
, Billing.CenterName
, datepart(year,Billing.TransDate) as Year
, datepart(month,Billing.TransDate) as Month
,Sum(Billing.Amount) as Amount
from Billing
Where datepart(year,Billing.TransDate)=@year
and Billing.CompanyName<>''
and Billing.CenterName<>''
and Billing.HasDeleted=0
Group By Billing.CompanyName,Billing.CenterName,datepart(year,Billing.TransDate),datepart(month,Billing.TransDate)
) as b
Left Join
(
Select SaleTarget.CompanyName 
,SaleTarget.CenterName 
, datepart(year,SaleTarget.Month) as Year
, datepart(month,SaleTarget.Month) as Month
,Sum(SaleTarget.Amount) as TargetAmount
from SaleTarget
where SaleTarget.DepartmentName=''
Group By SaleTarget.CompanyName,SaleTarget.CenterName , datepart(year,SaleTarget.Month),datepart(month,SaleTarget.Month)
) as t
on b.CompanyName=t.CompanyName 
and b.CenterName=t.CenterName
and b.Year=t.Year
and b.Month=t.Month
");
        }
        void CompanyYearReport()
        {
            var sqlTxt = string.Format(
@"Select b.CompanyName 
,b.Year,b.Month
,b.Amount 
,t.CompanyName as tName
,t.TargetAmount 
,Cast(Round(100*b.Amount /t.TargetAmount,2) as varchar)+'%' as Rate
from 
(
Select Billing.CompanyName
, datepart(year,Billing.TransDate) as Year
, datepart(month,Billing.TransDate) as Month
,Sum(Billing.Amount) as Amount
from Billing
Where datepart(year,Billing.TransDate)=@year
and Billing.CompanyName<>''
and Billing.HasDeleted=0
Group By Billing.CompanyName,datepart(year,Billing.TransDate),datepart(month,Billing.TransDate)
) as b
Left Join
(
Select SaleTarget.CompanyName 
, datepart(year,SaleTarget.Month) as Year
, datepart(month,SaleTarget.Month) as Month
,Sum(SaleTarget.Amount) as TargetAmount
from SaleTarget
where SaleTarget.CenterName=''
and  SaleTarget.DepartmentName=''
Group By SaleTarget.CompanyName, datepart(year,SaleTarget.Month),datepart(month,SaleTarget.Month)
) as t
on b.CompanyName=t.CompanyName 
and b.Year=t.Year
and b.Month=t.Month



");
        }
    }
}